
from sqlalchemy import and_
from datetime import datetime

from app.dao import Session
from app.models import Group,User, Hub, GroupDetail, Database
from app.parameter import  QueryDatabasesParam,QueryHubsParam,CreateDatabaseParam,DeleteHubParam,QueryHubParam
from app.utils import Response,  testMysqlConnect
from app.constant import ERRORCODE
from utils import DEBUG,ERROR


class DatabaseService():
    SUPPORT_DATABASE = ["mysql"]
    
    @staticmethod
    def judgeGroup(userId :int, groupId :int, session):
        return session.query(Group).filter(and_(Group.user_id == userId, Group.group_id == groupId)).first()

    @staticmethod
    def judgeHub(userId :int, hubId :int, session):
        return session.query(Hub, Group).join(Group, Hub.group_id == Group.group_id).filter(and_(Hub.id == hubId, Group.user_id == userId)).first()
    
    @staticmethod
    def judgeDatabase(databaseId :int, userId :int, session = None):
        try:
            if session != None:
                return session.query(Database).join(Hub, Database.hub_id == Hub.id).join(Group, Hub.group_id == Group.group_id).filter(and_(Database.id == databaseId, Group.user_id == userId)).first()
            with Session() as session:
                return  session.query(Database).join(Hub, Database.hub_id == Hub.id).join(Group, Hub.group_id == Group.group_id).filter(and_(Database.id == databaseId, Group.user_id == userId)).first()
        except Exception as e:
            ERROR(e)
            return Response(ERRORCODE.RESPONSE_DATABASE_OPERATION_ERROR, None)
    
    
    @staticmethod
    def queryDatabases(queryDatabasesParam :QueryDatabasesParam, userId: int):
        try:
            with Session() as session:
                res = DatabaseService.judgeHub(userId, queryDatabasesParam.hubId, session)
                if res == None:
                    return Response(ERRORCODE.REQUEST_NOT_FOUND_ERROR, message="Not found hub!")
                
                databases = session.query(Database).filter(Database.hub_id == queryDatabasesParam.hubId).all()
                databases = [database.to_dict() for database in databases]
                
                return Response(ERRORCODE.RESPONSE_SUCCESS, databases)
        except Exception as e:
            ERROR(e)
            return Response(ERRORCODE.RESPONSE_DATABASE_OPERATION_ERROR, None)
    
    @staticmethod
    def createDatabase(createDatabaseParam :CreateDatabaseParam, userId: int):
        try:
            if createDatabaseParam.type not in DatabaseService.SUPPORT_DATABASE:
                return Response(ERRORCODE.REQUEST_ERROR, message = "Not support the database type:" + createDatabaseParam.type) 
            with Session() as session:
                res = DatabaseService.judgeHub(userId, createDatabaseParam.hubId, session)
                if res == None:
                    return Response(ERRORCODE.REQUEST_NOT_FOUND_ERROR, message="not found group!")
                
                DEBUG(res)
                group = res[1].to_dict()
                DEBUG(group)
                if group["permission"] < 1:
                    return Response(ERRORCODE.REQUEST_HAVE_NO_PERMISSION)
                
                if not testMysqlConnect(createDatabaseParam.ip, createDatabaseParam.port, createDatabaseParam.database, createDatabaseParam.username, createDatabaseParam.password):
                    return Response(ERRORCODE.REQUEST_ERROR, message = "数据库连接测试失败！")
                
                database = Database(ip = createDatabaseParam.ip, port = createDatabaseParam.port, database = createDatabaseParam.database, \
                    username = createDatabaseParam.username, password = createDatabaseParam.password, \
                        type = createDatabaseParam.type, user_id = userId, hub_id = createDatabaseParam.hubId)
                
                session.add(database)
                session.commit()
                return Response(ERRORCODE.RESPONSE_SUCCESS)
        except Exception as e:
            ERROR(e)
            return Response(ERRORCODE.RESPONSE_DATABASE_OPERATION_ERROR, None)
        
    @staticmethod
    def deleteDatabase(deleteHubParam :DeleteHubParam, userId: int):
        try:
            with Session() as session:
                # 查询
                hub = session.query(Hub)\
                    .filter(and_(Hub.id == deleteHubParam.hubId, Hub.user_id == deleteHubParam.userId)).delete()
                if hub == None:
                    return Response(ERRORCODE.REQUEST_HAVE_NO_PERMISSION)
                print(hub)
                session.commit()
                return Response(ERRORCODE.RESPONSE_SUCCESS,hub)
        except Exception as e:
            ERROR(e)
            return Response(ERRORCODE.RESPONSE_DATABASE_OPERATION_ERROR, None)

